#!/usr/bin/perl -w
use strict;
use Getopt::Long;



#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888

#	usage

#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888

my $usage = <<'USAGE';

USAGE:

    panda_retrieve_fasta.pl
                              --temp_dir /local/share/databases/panda
                              --results /local/results/results.fa
                              --db_login "panda_db panda_user"
                              --PI1 "6106"
                              --PI2 "1667807410"
                              [--help]
    
    'panda_db' is the database name and 'panda_user' is a valid user
    results should be read/write accessible to postgreSQL
    PI1 and PI2 specify the two halves of the panda number.
                       
    This script retrieves an sequence from the panda database given a panda
        number.

USAGE





#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888

#	Get options

#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888

# options variables
my $DB_LOGIN;
my $PI1 = '';
my $PI2 = '';
my $results = '';
my $RESULTS_DIR = '';
my $help;
GetOptions(	'help'			=> \$help,
			'db_login=s'	=> \$DB_LOGIN, 
			'PI1=s'			=> \$PI1, 
			'PI2=s'			=> \$PI2, 
			'results=s'		=> \$results, 
			'temp_dir=s'	=> \$TEMP_DIR);

die $usage if ($help);
die "\nERROR:\n\tMissing Argument:\n" .$usage
				unless ($PI1 && $PI2 && $results && $DB_LOGIN && $TEMP_DIR);

# remove trailing slash
$TEMP_DIR =~ s/\/$//;



#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888

#	Write out temporary SQL file using __DATA__ as a template

#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888
open (TMP_FILE, ">$TEMP_DIR/tmp.sql") or die "Couldn't open $TEMP_DIR/tmp.sql\n";
while (<DATA>)
{
	s/PI1/$PI1/;
	s/PI2/$PI2/;
	s/RESULTS/$results/;
	print TMP_FILE;
}








#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888

#	Call postgreSQL and cleanup

#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888

system ("psql ".$DB_LOGIN. " -f $TEMP_DIR/tmp.sql");
system ("rm $TEMP_DIR/tmp.sql");







#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888

#	SQL instructions for PSQL follow

#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888
__DATA__
-- SQL instructions for PSQL:
\t
\f ''
\a
\pset recordsep ''

--function to break strings into 80 columns
DROP FUNCTION fasta_str(TEXT);
CREATE FUNCTION fasta_str(TEXT) RETURNS TEXT AS '
DECLARE
    index  INTEGER;
    length INTEGER;
    temp  TEXT;
	sequence ALIAS FOR $1;
BEGIN
	temp := '''';
	index := 81;
	length := char_length(sequence);
	WHILE index < length LOOP
		temp := temp || substr(sequence, index - 80, 80) || ''\\n'';
		index := index + 80;
	END LOOP;	

	if index - 80 < length THEN
		temp := temp || substr(sequence, index - 80);
	END IF;
	RETURN temp;

END;
' LANGUAGE 'plpgsql';



-- function to aggregate strings
DROP FUNCTION ascii_one_aggregate (TEXT,TEXT);
CREATE function ascii_one_aggregate(TEXT,TEXT) returns TEXT as '
begin
  if (length($1) > 0 ) then
    return $1 || chr(1) || $2;
  else
    return $2;
  end if;
end;
' language 'plpgsql';
-- create the aggregate function 
COMMENT ON FUNCTION ascii_one_aggregate ( TEXT,TEXT ) IS
		'Return aggregates concatenated with an ascii 1 character ';

DROP AGGREGATE ascii_one_aggregate TEXT;
CREATE AGGREGATE ascii_one_aggregate
	(basetype=TEXT, sfunc=ascii_one_aggregate, stype=TEXT, initcond='' );



DROP FUNCTION firstof (TEXT,TEXT);
CREATE FUNCTION firstof (TEXT,TEXT) RETURNS TEXT AS '
BEGIN
  IF length($1) > 0 THEN
    RETURN $1;
  ELSE
    RETURN $2;
  END IF;
END;
'
LANGUAGE 'plpgsql' WITH ( iscachable ); 

DROP AGGREGATE firstof TEXT;
CREATE AGGREGATE firstof ( BASETYPE = text, SFUNC = firstof, STYPE = text, INITCOND = '' );
COMMENT ON AGGREGATE firstof TEXT IS 'Return first item in aggregate';
COMMENT ON FUNCTION firstof ( TEXT,TEXT ) IS 'Return first item in aggregate';



SELECT '***************************************************';
SELECT 'BEGIN  list matching sequences from Panda at ', current_time;
\o RESULTS
SELECT  '>'   ||
		ascii_one_aggregate(acc_text) || '\n' || 
		fasta_str(firstof(sequence)) || '\n' 
			FROM accessions natural join sequences
			WHERE sequences.pi1=oid(PI1) and sequences.pi2=oid(PI2) 
			GROUP BY sequences.pi1, sequences.pi2;
SELECT 'FINISH at ', current_time;
\o
SELECT '***************************************************';
